<?php 
  require_once("../includes/config.php"); 
  $length = 20;
  
  if(isset($_GET['c']))
  {
    echo 0;
    die();
  }
  if( isset($_GET['page'])  && !empty($_GET['page'] ))
  {
    $page =  filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
    $start = $page*20 - 20;
  }
  else
  {
    $start = 0;
    $page = 1;
  }

  /*$sql = 'SELECT a.id AS apartment__id,a.* , c. *,clients.*,companies.*
          FROM apartments a
          LEFT JOIN (

          SELECT contract_id, MAX( updated_at ) MaxDate
          FROM contracts
          GROUP BY apartment_id
          )MaxDates ON a.id = MaxDates.apartment_id
          LEFT JOIN 
            contracts c ON MaxDates.apartment_id = c.apartment_id
          AND 
            MaxDates.MaxDate = c.updated_at 
          LEFT JOIN
            clients ON clients.id = c.client_id 
          LEFT JOIN 
            companies ON  c.company_id=companies.company_id  
          WHERE a.owner_id=?';*/
  /*$sqlCount = 'SELECT COUNT(*) FROM apartments a
          LEFT JOIN (

          SELECT apartment_id, MAX( updated_at ) MaxDate
          FROM contracts
          GROUP BY apartment_id
          )MaxDates ON a.id = MaxDates.apartment_id
          LEFT JOIN contracts c ON MaxDates.apartment_id = c.apartment_id
          AND MaxDates.MaxDate = c.updated_at where a.owner_id=?';*/
         
  $sql = 'SELECT a.id AS apartment__id,a.* FROM apartments a WHERE a.owner_id=?';  
  $sqlCount = 'SELECT COUNT(*) FROM apartments a WHERE a.owner_id=?';
  
  $binds = array();
  
  $binds['owner_id'] = $_SESSION['user_id'];

  if(isset($_GET['apart_id']) && $_GET['apart_id'] != -1) 
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = " ";
           
    $binds['id'] = $_GET['apart_id'];
    $sql .= $and."a.id  = ?";
    $sqlCount .= $and."a.id  = ?";
  } 
        
  if((isset($_GET['apart_status']) && $_GET['apart_status'] != -1)||$_SESSION['r']==4 )
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
    if ($_SESSION['r']==4)
	{
	$binds['status'] = 8;
	}
	else
	{
		//if free apartment is chosen, show reserved apartments as well
		if(isset($_GET['apart_status']) && $_GET['apart_status'] == 2)
		{
			$binds['status'] = 2;
			$binds['status2'] = 6;
		}
		else
			$binds['status'] = $_GET['apart_status'];
	}
	if(isset($_GET['apart_status']) && $_GET['apart_status'] == 2)
	{
		$sql .= $and."(a.status  = ? || a.status = ?)";
		$sqlCount .= $and."(a.status  = ? || a.status = ?)";
	}
	else
	{
		$sql .= $and."a.status  = ?";
		$sqlCount .= $and."a.status  = ?";
	}
  }
  else
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $sql .= $and."a.status  != 7";
    $sqlCount .= $and."a.status  != 7";
  }

  if(!empty($_GET['rooms#']))
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['rooms#'] = $_GET['rooms#'];
    $sql .= $and."rooms  = ?";
    $sqlCount .= $and."rooms = ?";
  }
        
  if(!empty($_GET['floor#']))
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['floor#'] = $_GET['floor#'];
    $sql .= $and."floor = ?";
    $sqlCount .= $and."floor = ?";
  }
  
  if(!empty($_GET['room_name']))
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['room_name'] = $_GET['room_name'];
    $sql .= $and."apart_name = ?";
    $sqlCount .= $and."apart_name = ?";
  }
        
  if(isset($_GET['apart_type']) && $_GET['apart_type'] != -1)
  {
    if(!empty($binds))
      $and = " and ";
    else
      $and = "  ";
          
    $binds['apart_type'] = $_GET['apart_type'];
    $sql .= $and."room_type = ?";
    $sqlCount .= $and."room_type = ?";
  }
  $sql .= " ORDER BY a.apart_name ASC LIMIT ?,?";
  $binds['start'] = $start;
  $binds['end'] = $length;
  
  //dump($sql);
  $apartments = query($sql,$binds);
  $Count = query($sqlCount,$binds);
  $totalPages = $Count[0]['COUNT(*)'];
  
  //array to hold the whole data
  $aprts = array();
  //SELECTING appropriate contract for each apartment if any
  foreach($apartments as $apart)
  {
    $in_contracts = query("SELECT * FROM contract_apartments LEFT JOIN contracts ON 
    contract_apartments.contract_id = contracts.contract_id 
    LEFT JOIN
      clients ON clients.id = contracts.client_id 
    LEFT JOIN 
      companies ON  contracts.company_id=companies.company_id 
    WHERE apartment_id = ? and contract_status = 2",$apart['apartment__id']);
    
    
    //if the chosen apartment has a contract cover this period
    if(!empty($in_contracts))
    {
      $contract = $in_contracts[0];
      $apart = array_merge($apart, $contract);
      $aprts[] = $apart;
    }
    else
    {
      //check for pre-booked contracts ordered BY apartment entrance date
      $booked_contracts = query("SELECT * FROM contract_apartments 
      LEFT JOIN contracts ON 
        contract_apartments.contract_id = contracts.contract_id 
      LEFT JOIN
        clients ON clients.id = contracts.client_id 
      LEFT JOIN 
        companies ON contracts.company_id=companies.company_id 
      WHERE apartment_id = ? and contract_status = 1 ORDER BY mildai_from",$apart['apartment__id']);
      
      if(!empty($booked_contracts))
      {
        $contract = $booked_contracts[0];
        $apart = array_merge($apart, $contract);
        $aprts[] = $apart;
      }
      else
      {
        $aprts[] = $apart;//define required fields with empty records to avoid errors
      }
    }
  }
  
  //Getting Statistics
  $N=query("SELECT COUNT(*) FROM apartments WHERE owner_id = ? and status != 7 ",$_SESSION['user_id']);
  $M=query("SELECT COUNT(*) FROM apartments WHERE status=? and owner_id = ?",1,$_SESSION['user_id']);
	$F=query("SELECT COUNT(*) FROM apartments WHERE  (status=? || status = ?)  and owner_id = ?",2,6,$_SESSION['user_id']);
  $T=query("SELECT COUNT(*) FROM apartments WHERE  status=? and owner_id = ?",3,$_SESSION['user_id']);
	$S=query("SELECT COUNT(*) FROM apartments WHERE  status=? and owner_id = ?",4,$_SESSION['user_id']);
	$A=query("SELECT COUNT(*) FROM apartments WHERE  status=? and owner_id = ?",5,$_SESSION['user_id']);
	$B=query("SELECT COUNT(*) FROM apartments WHERE  status=? and owner_id = ?",6,$_SESSION['user_id']);
	$R=query("SELECT COUNT(*) FROM apartments WHERE  status=? and owner_id = ?",8,$_SESSION['user_id']);
	$R=query("SELECT COUNT(*) FROM apartments WHERE  status=? and owner_id = ?",9,$_SESSION['user_id']);
  $types = query("SELECT * FROM unit_type");

  render("apartments.php", array("title"=>"إدارة الوحدات","aprts"=>$aprts,"totalPages"=>$totalPages,"page"=>$page,"binds"=>$binds,"cooler_type"=>$cooler_type,"N"=>$N,"M"=>$M,"F"=>$F,"T"=>$T,"S"=>$S,"A"=>$A,"B"=>$B,"R"=>$R,"types"=>$types));


?>